package com.howard.tools.file;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jett.transform.ExcelTransformer;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eobjects.metamodel.DataContext;
import org.eobjects.metamodel.DataContextFactory;
import org.eobjects.metamodel.data.DataSet;
import org.eobjects.metamodel.query.Query;
import org.eobjects.metamodel.schema.Column;
import org.eobjects.metamodel.schema.Schema;
import org.eobjects.metamodel.schema.Table;

import com.howard.tools.file.bo.ExcelMultipleBeanTemplate;
import com.howard.tools.file.bo.ExcelSingleBeanTemplate;
import com.howard.tools.file.bo.ExcelTemplate;
import com.howard.tools.file.bo.SheetObject;

public abstract class ExcelHelper extends FileHelper {
	
	
	public static SimpleDateFormat DATE_FORMATE = new SimpleDateFormat("yyyy-MM-dd");
	
	/**
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     */
    public void readExcel()
    {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File("/home/zht/test.xls"); //创建文件对象
            FileInputStream is = new FileInputStream(excelFile); //文件流
            Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    //遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        switch(cellType) {
                            case Cell.CELL_TYPE_STRING: //文本
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                if(DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue()); //日期型
                                }
                                else {
                                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: //空白
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_ERROR: //错误
                                cellValue = "错误";
                                break;
                            case Cell.CELL_TYPE_FORMULA: //公式
                                cellValue = "错误";
                                break;
                            default:
                                cellValue = "错误";
                        }
//                        System.out.print(cellValue + "    ");
                    }
//                    System.out.println();
                }
            }

        }
        catch (Exception e) {
            e.printStackTrace();
        }

    }
	
	public static void generateExcel(InputStream templateFileIs, String outFilename, ExcelTemplate excelTemplate) throws IOException,
			InvalidFormatException {
		FileOutputStream fileOut = null;
		InputStream fileIn = null;
		Workbook workbook = null;
		try {
			File outputFile = new File(outFilename);
			if(outputFile.exists() == false){
				if(outputFile.getParentFile().exists() == false){
					outputFile.getParentFile().mkdirs();
				}
				outputFile.createNewFile();
			}
			fileOut = new FileOutputStream(outputFile);
//			fileIn = new BufferedInputStream(ExcelHelper.class.getResourceAsStream(templateFileName));
			fileIn = new BufferedInputStream(templateFileIs);

			ExcelTransformer transformer = new ExcelTransformer();
			if(excelTemplate instanceof ExcelSingleBeanTemplate){
				ExcelSingleBeanTemplate excelSingleBeanTemplate = (ExcelSingleBeanTemplate)excelTemplate;
				workbook = transformer.transform(fileIn, excelSingleBeanTemplate.getBeansMap());
			}
			else if(excelTemplate instanceof ExcelMultipleBeanTemplate){
				ExcelMultipleBeanTemplate excelMultipleBeanTemplate = (ExcelMultipleBeanTemplate)excelTemplate;
				workbook = transformer.transform(fileIn, excelMultipleBeanTemplate.getTemplateSheetNameList(),
						excelMultipleBeanTemplate.getResultSheetList(), excelMultipleBeanTemplate.getBeansMapList());
			}

			workbook.write(fileOut);
			fileOut.close();
		} finally {
			try {
				if (fileIn != null)
					fileIn.close();
			} catch (IOException ignored) {
			}
			try {
				if (fileOut != null)
					fileOut.close();
			} catch (IOException ignored) {
			}
		}
	}
	
	public static List<SheetObject> readExcel(InputStream excelInputStream) throws Exception{
		List<SheetObject> sheetObjList = new ArrayList<SheetObject>();
		
		Workbook wb = WorkbookFactory.create(excelInputStream);
		for(int sheetNo = 0 ; sheetNo < wb.getNumberOfSheets(); sheetNo ++){
			Sheet sheet = wb.getSheetAt(sheetNo);
			int rowCount = sheet.getLastRowNum();
			int colCount = sheet.getRow(0).getLastCellNum();
			String[][] sheetData = new String[rowCount+1][colCount];
			
			for (int rowNo = 0; rowNo <= sheet.getLastRowNum(); rowNo++) {
				Row row = sheet.getRow(rowNo);
				for (int colNo = row.getFirstCellNum(); colNo <= row.getLastCellNum(); colNo++) {
					Cell cell = row.getCell(colNo);
					if(cell != null){
						sheetData[rowNo][colNo] = cell.toString().trim();
					}
				}
			}
			SheetObject sheetObj = new SheetObject();
			sheetObj.setName(sheet.getSheetName());
			sheetObj.setData(sheetData);
			sheetObjList.add(sheetObj);
		}
		return sheetObjList;
	}
	
	public static List<SheetObject> readExcel(File excelFile) throws Exception{
		return readExcel(new FileInputStream(excelFile));
	}
	
	public static String getCellStringValue(Cell cell) {
		if(cell == null){
			return null;
		}
		return cell.toString().trim();
	}
	
	public static String getCellStringValue(Sheet sheet, int rowNo, int colNo) {
		Row row = sheet.getRow(rowNo);
		if(row == null){
			return null;
		}
		Cell cell = row.getCell(colNo);
		return getCellStringValue(cell);
	}
	
	public static Cell setCell(Sheet sheet, int rowNo, int colNo, String value) throws Exception{

		Row row = sheet.getRow(rowNo);
		if (row == null) {
			row = sheet.createRow(rowNo);
		}
		Cell cell = row.getCell(colNo);
		if (cell == null) {
			cell = row.createCell(colNo);
		}
//		cell.setCellValue(value);
		
		int cellType = cell.getCellType();
        switch(cellType) {
            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                if(DateUtil.isCellDateFormatted(cell)) {
                	cell.setCellValue(DATE_FORMATE.parse(value));
                }
                else {
                	cell.setCellValue(Double.parseDouble(value));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
            	cell.setCellValue(Boolean.parseBoolean(value));
                break;
            default:
                cell.setCellValue(value);
        }
		return cell;
	}

	public static Cell setCell(Sheet sheet, int rowNo, int colNo,
			CellStyle cellStyle, String value) throws Exception{

		Cell cell = setCell(sheet, rowNo, colNo, value);
		cell.setCellStyle(cellStyle);
		
		return cell;
	}
	
	public static Workbook getWorkBook(File excelFile) throws Exception{
		String fileName = excelFile.getName();
		String surfix = fileName.substring(fileName.lastIndexOf(".")+1);
		if("xls".equalsIgnoreCase(surfix)){
			return new HSSFWorkbook(new FileInputStream(excelFile));
		}
		if("xlsx".equalsIgnoreCase(surfix)){
			return new XSSFWorkbook(new FileInputStream(excelFile));
		}
		return null;
	}
	
	public static void readExcelInSql() throws Exception{
		File myFile = new File("d:/tmp/Implementation Data Conversion UAT schedule 20140214.xlsx");

		DataContext dataContext = DataContextFactory.createExcelDataContext(myFile);
		Schema schema = dataContext.getDefaultSchema();
		Table[] tables = schema.getTables();

		Table table = tables[0];
		
		Column tableColumn = table.getColumn(1);
		Column sysColumn = table.getColumn(9);
		Column startDtColumn = table.getColumn(10);
		Query q = dataContext.query().from(table).select(tableColumn,startDtColumn).where(sysColumn).eq("CT").where(startDtColumn).eq("01/03/2014").toQuery();
		System.out.println(q.toString());
		
		DataSet ds = dataContext.executeQuery(q);
		while (ds.next()) {
			Row row = ds.getRow();
			String tableName = (String) row.getValue(tableColumn);
			String startDtStr = (String) row.getValue(startDtColumn);
			System.out.println("tableColumn: " + tableName+",startDtStr="+startDtStr);
		}
	}
	
	public static void main(String[] args) {

		// String[] templateSheetNameArray = new String[8];
		// Arrays.fill(templateSheetNameArray, "Division");
		// System.out.println(Arrays.asList(templateSheetNameArray));

		InputStream fileIn = null;
		try {
			
//			List<SheetObject> sheetList = readExcel(new File("D:/workspace4.3/howard-app-csss2-rule/templates/CSSS2_Rule_Decision_tables.xlsx"));
//			for(SheetObject sheetObj: sheetList){
//				System.out.println("=======sheetName="+sheetObj.getName());
//				int row= 0;
//				for(String[] rowData: sheetObj.getData()){
//					System.out.println("===========row"+row);
//					for(String cellData: rowData){
//						System.out.print(cellData+",");
//					}
//					System.out.println();
//					row ++;
//				}
//			}
			
//			File excelFile = new File("G:/tmp/input/大连/201311290938_{68C6FAC1-D57F-4DFE-8512-83E4498F2D81}.xls");
//			System.out.println(excelFile.exists());
//			FileInputStream is = new FileInputStream(excelFile);
//			Workbook wb = WorkbookFactory.create(is);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected static Map<String, Object> getBeansMap() {
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("testBean1", "Hello World!");
		beans.put("testBean2", "JETT");
		List<Map<String, Object>> prodList = new ArrayList<Map<String, Object>>();
		Map<String, Object> prod1 = new HashMap<String, Object>();
		prod1.put("no", "1");
		prod1.put("type", "type1");
		prod1.put("price", "11");
		prod1.put("price2", new Double("123.456"));
		prod1.put("remark", "remark1");
		Map<String, Object> prod2 = new HashMap<String, Object>();
		prod2.put("no", "2");
		prod2.put("type", "type2");
		prod2.put("price", "22");
		prod2.put("price2", new Date());
		prod2.put("remark", Boolean.TRUE);
		Map<String, Object> prod3 = new HashMap<String, Object>();
		prod3.put("no", "3");
		prod3.put("type", "type3");
		prod3.put("price", "33");
		prod3.put("price2", "333");
		prod3.put("remark", "remark3214121243123412341234");
		prodList.add(prod1);
		prodList.add(prod2);
		prodList.add(prod3);
		beans.put("productList", prodList);
		return beans;
	}
}
